# coding=utf-8

import sqlite3
from utils.utils import GenRandomStr

class User_Manager:
    '''
        用户管理的数据库接口
        维护两张表：
            admin:|ID|账号|密码|已使用次数|已使用时间|
            users:|ID|账号|密码|联系方式|token|已使用次数|剩余次数|备注|
    '''
    '''宏定义'''
    OK="ok"
    DUPLICATED_USERNAME="user already exist"
    DB_FULL="db full"
    INVALID_SYNTAX="syntax error"
    UNKNOWN="unknown eror"
    NO_TOKEN="insufficent token"      #功德没了
    NO_ACCOUNT="user invalid"    #找不到用户
    INCORRECT_PASSWD="incorrect account or password"      #密码错误

    def __init__(self, local_db=''):
        '''
        初始化数据库接口
        :param local_db: 本地数据库路径
        '''
        self.conn = sqlite3.connect(local_db)
        self.cursor = self.conn.cursor()
    def InitDb(self):
        #   判断数据库是否存在,不存在则初始化管理员表格
        self.cursor.execute('CREATE TABLE IF NOT EXISTS TABLE_ADMIN(\
            id          INTEGER        PRIMARY KEY AUTOINCREMENT,\
            account     TEXT        NOT NULL,\
            password    TEXT        NOT NULL,\
            token_str   TEXT,\
            token_used  REAL);')     #   初始化管理员数据库
        self.cursor.execute('CREATE TABLE IF NOT EXISTS TABLE_USER(\
            id          INTEGER        PRIMARY KEY NOT NULL,\
            account     TEXT        NOT NULL,\
            password    TEXT        NOT NULL,\
            contact    TEXT        NOT NULL,\
            token_str   TEXT,\
            token_used  REAL,\
            token_remained REAL NOT NULL,\
            HINT TEXT);')      #   初始化本地用户数据库
        #   完成
        self.conn.commit()

    def AddUser(self,account="anonymous",user_type="USER",passwd="123456",contact="",token_remained=0):
        '''
        添加一个用户
        :param user: 要登记到数据库中的用户
        :return: 添加是否成功
        '''

        '''检测数据库中account的可用性'''
        ret=self.cursor.execute("SELECT account FROM TABLE_{} WHERE account = \"{}\";".format(user_type,account))
        for i in ret: return User_Manager.DUPLICATED_USERNAME
        '''没有重名，登记该用户'''
        add_Cmd="INSERT INTO TABLE_{} (account,password,contact,token_str,token_used,token_remained) VALUES (\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\');"\
            .format(user_type,account,passwd,contact,GenRandomStr(16),0,token_remained)
        self.cursor.execute(add_Cmd)
        self.conn.commit()

        return User_Manager.OK
    def ModifyPasswd(self,account,old_passwd,new_passwd,user_type='USER'):
        '''
        修改用户密码
        :param account:
        :param old_passwd:
        :param new_passwd:
        :param user_type:
        :return:
        '''
        if self.VerifyPasswd(account=account, passwd=old_passwd, user_type=user_type) == User_Manager.OK:
            self.cursor.execute("UPDATE TABLE_{} SET password = \"{}\" WHERE account = \"{}\"".format(user_type,new_passwd, account))
            self.conn.commit()
            return User_Manager.OK
        else:
            return User_Manager.INCORRECT_PASSWD
    def DelUser(self,account='',user_type='USER',passwd=''):
        '''
        删除用户
        :param account:
        :param user_type:
        :param passwd:
        :return:
        '''
        if self.VerifyPasswd(account=account,passwd=passwd,user_type=user_type)==User_Manager.OK:
            return self.__DelUser(account,user_type=user_type)
        return User_Manager.INCORRECT_PASSWD
    def __DelUser(self,account='',user_type='USER'):
        self.cursor.execute("DELETE FROM TABLE_{} WHERE account = \"{}\";".format(user_type,account))
        self.conn.commit()
        return User_Manager.OK

    def VerifyPasswd(self,account,passwd,user_type='USER'):
        '''
        密码校验
        :param account:
        :param passwd:
        :param user_type:
        :return:
        '''
        passwd_Db=''
        ret=self.cursor.execute("SELECT password FROM TABLE_{} WHERE account = \"{}\"".format(user_type,account))
        for i in ret: passwd_Db=i[0]
        if passwd==passwd_Db: return User_Manager.OK
        return User_Manager.INCORRECT_PASSWD


    def UpdateToken(self,token_str="",user_type='USER',token_Cost=0):
        '''
        更新Token
        :param token_Cost:  花费token为正，充值token为负
        :return: [成功Flag]，[token对应用户名]，[token对应消耗值]
        '''
        ''' 首先定位到这个用户'''
        #   没有指定用户，那就识别Token
        account=''
        token_Used=0
        token_Remained=0
        #print(token_str)
        if token_str=="":
            return User_Manager.NO_ACCOUNT,'',0
        ret = self.cursor.execute("SELECT account FROM TABLE_{} WHERE token_str = \"{}\";".format(user_type,token_str))
        for i in ret: account=i[0]      #获取用户名
        if account == '': return User_Manager.NO_ACCOUNT,'',0
        ret = self.cursor.execute("SELECT token_used FROM TABLE_{} WHERE token_str = \"{}\";".format(user_type,token_str))
        for i in ret:  token_Used=i[0]  #获取用户当前Token
        ret = self.cursor.execute(
            "SELECT token_remained FROM TABLE_{} WHERE token_str = \"{}\";".format(user_type, token_str))
        for i in ret:  token_Remained = i[0]  # 获取用户当前Token数量
        if token_Remained<0 and token_Cost>0:
            return User_Manager.NO_TOKEN,account,token_Remained
        else:
            token_Remained-=token_Cost
            self.cursor.execute(
                "UPDATE TABLE_{} SET token_remained = {} WHERE token_str = \"{}\";".format(user_type,token_Remained, token_str))
        #   基于Token更新剩余token 量
        if token_Cost>0: token_Used+=token_Cost
        self.cursor.execute("UPDATE TABLE_{} SET token_used = {} WHERE token_str = \"{}\";".format(user_type,token_Used,token_str))
        self.conn.commit()
        return User_Manager.OK,account,token_Remained

    def GetToken(self,account,passwd,user_type='USER'):
        '''
        获取指定用户名下的Token
        :param account: 用户名
        :param passwd: 密码
        :return:
        '''
        findUserCmd = "SELECT password FROM TABLE_{} WHERE account = \"{}\";".format(user_type,account)
        ret=self.cursor.execute(findUserCmd)
        for i in ret:
            if passwd != i[0]: return User_Manager.INCORRECT_PASSWD,''
        ret =self.cursor.execute("SELECT token_str FROM TABLE_{} WHERE account = \"{}\";".format(user_type,account))
        for token in ret: return User_Manager.OK,token[0]
        return User_Manager.NO_ACCOUNT,''

    def __del__(self):
        '''
        程序结束，释放数据库
        :return:
        '''
        self.conn.commit()
        self.conn.close()

if __name__ == '__main__':
    u = User_Manager('users.db')
    u.InitDb()
    ret = u.AddUser(account="zhangdeyu1",user_type='USER',passwd="123",contact="18810577546")
    ret=u.ModifyPasswd('zhangdeyu1','123','asd')
    # print(ret)
